﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using LBSoft.Common.DotNetExt;
using LBSoft.Common.DotNetUI;

namespace LBSoft.Common.DotNetOutExcel
{
    public class ExportExcelHelper
    {
        public int ColumnWidth(double x)
        {
            return Convert.ToInt32((256 * 40 * x) / 30);
        }

        /// <summary>
        /// 导出表格
        /// </summary>
        public void GetExcel(DataTable dt, string filepath, string sheetName)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);
            try
            {
                //内容公共样式
                ICellStyle style = hssfworkbook.CreateCellStyle();
                //自动换行
                style.WrapText = true;
                //设置单元格的样式：水平对齐居中
                style.Alignment = HorizontalAlignment.Center;
                //设置单元格的样式：垂直居中
                style.VerticalAlignment = VerticalAlignment.Center;
                //设置单元格样式为文本格式
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");


                //添加表头
                IRow row1 = sheet1.CreateRow(0);
                row1.HeightInPoints = 30;
                //表头
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell celldt = row1.CreateCell(row1.Cells.Count, CellType.String);
                    celldt.SetCellValue(dt.Columns[i].ToString());
                    sheet1.SetColumnWidth(i, 25 * 256);//列宽
                    //自定义列宽设置
                    if (dt.Columns[i].ToString() == "序号")
                    {
                        sheet1.SetColumnWidth(i, ColumnWidth(10));//列宽
                    }

                    celldt.CellStyle = style;
                }

                //内容
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow rowcontent = sheet1.CreateRow(i + 1);
                    rowcontent.HeightInPoints = 28;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell currentCell = rowcontent.CreateCell(j, CellType.String);
                        currentCell.SetCellValue(dt.Rows[i][j].ToString());
                        currentCell.CellStyle = style;
                    }
                }

                using (Stream stream = File.OpenWrite(filepath))
                {
                    hssfworkbook.Write(stream);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 导出复杂表头
        /// </summary>
        public void GetExcel_ExportPerson2(DataSet dt, string filepath, string sheetName, string excelTitle)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);
            //内容公共样式
            ICellStyle style = hssfworkbook.CreateCellStyle();
            //自动换行
            style.WrapText = true;
            //设置单元格样式为文本格式
            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            //边界线
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor = HSSFColor.Black.Index;
            style.RightBorderColor = HSSFColor.Black.Index;
            style.TopBorderColor = HSSFColor.Black.Index;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //字体
            IFont FontBoss = hssfworkbook.CreateFont();
            FontBoss.FontHeightInPoints = 11;
            FontBoss.FontName = "宋体";
            style.SetFont(FontBoss);

            //内容公共样式
            ICellStyle StyleLeft = hssfworkbook.CreateCellStyle();
            //自动换行
            StyleLeft.WrapText = true;
            //设置单元格样式为文本格式
            StyleLeft.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");

            StyleLeft.Alignment = HorizontalAlignment.Left;
            StyleLeft.VerticalAlignment = VerticalAlignment.Center;
            //字体
            IFont FontBoss1 = hssfworkbook.CreateFont();
            FontBoss1.FontHeightInPoints = 10;
            FontBoss1.FontName = "宋体";
            StyleLeft.SetFont(FontBoss);

            //单位名称 项目部名称 制表人 制表日期 可以在传个Hashtable参数过来 currentCell.SetCellValue 赋值
            //表格合并单元格  sheet1.AddMergedRegion(new CellRangeAddress(row, row, 0, 5));

            try
            {
                int table = 0;
                int row = 0;
                for (int i = 0; i < dt.Tables.Count; i++)
                {
                    //表头
                    IRow row1 = sheet1.CreateRow(row);
                    row1.HeightInPoints = 30;
                    ICell row1_ICell = row1.CreateCell(row1.Cells.Count, CellType.String);
                    ICellStyle style1 = row1_ICell.CellStyle;
                    row1_ICell.SetCellValue(excelTitle); //表头名称
                    sheet1.AddMergedRegion(new CellRangeAddress(row, row, 0, 5));
                    row1_ICell.CellStyle = style;
                    ICellStyle stylehead = hssfworkbook.CreateCellStyle();
                    stylehead.Alignment = HorizontalAlignment.Center;
                    stylehead.WrapText = true;
                    IFont font = hssfworkbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "华文仿宋";
                    stylehead.SetFont(font);//HEAD 样式
                    row1.GetCell(0).CellStyle = stylehead;

                    //第二行
                    IRow row2 = sheet1.CreateRow(++row);  //定义行 第二行
                    row2.HeightInPoints = 30;
                    ICell row2_ICell = row2.CreateCell(0, CellType.String);
                    row2_ICell.SetCellValue("单位名称：");
                    row2_ICell.CellStyle = StyleLeft;

                    ICell row2_ICell2 = row2.CreateCell(3, CellType.String);
                    row2_ICell2.SetCellValue("项目部名称：");
                    row2_ICell2.CellStyle = StyleLeft;

                    sheet1.AddMergedRegion(new CellRangeAddress(row, row, 0, 2));
                    sheet1.AddMergedRegion(new CellRangeAddress(row, row, 3, 5));

                    //第三行
                    IRow row3 = sheet1.CreateRow(++row);  //定义行 第三行
                    row3.HeightInPoints = 30;
                    ICell row3_ICell = row3.CreateCell(0, CellType.String);
                    row3_ICell.SetCellValue("序号");
                    row3_ICell.CellStyle = style;
                    sheet1.SetColumnWidth(0, ColumnWidth(10));//列宽

                    ICell row3_ICell2 = row3.CreateCell(1, CellType.String);
                    row3_ICell2.SetCellValue("姓名");
                    row3_ICell2.CellStyle = style;
                    sheet1.SetColumnWidth(1, ColumnWidth(18));//列宽

                    ICell row3_ICell3 = row3.CreateCell(2, CellType.String);
                    row3_ICell3.SetCellValue("身份证号");
                    row3_ICell3.CellStyle = style;
                    sheet1.SetColumnWidth(2, ColumnWidth(18));//列宽

                    ICell row3_ICell4 = row3.CreateCell(3, CellType.String);
                    row3_ICell4.SetCellValue("性别");
                    row3_ICell4.CellStyle = style;
                    sheet1.SetColumnWidth(3, ColumnWidth(18));//列宽

                    ICell row3_ICell5 = row3.CreateCell(4, CellType.String);
                    row3_ICell5.SetCellValue("民族");
                    row3_ICell5.CellStyle = style;
                    sheet1.SetColumnWidth(4, ColumnWidth(18));//列宽

                    ICell row3_ICell6 = row3.CreateCell(5, CellType.String);
                    row3_ICell6.SetCellValue("联系电话");
                    row3_ICell6.CellStyle = style;
                    sheet1.SetColumnWidth(5, ColumnWidth(18));//列宽

                    row++;
                    for (int a = 0; a < dt.Tables[table].Rows.Count; a++)
                    {
                        IRow rowcontent = sheet1.CreateRow(row++);
                        int height = 0;
                        for (int j = 0; j < dt.Tables[table].Columns.Count; j++)
                        {
                            ICell currentCell = rowcontent.CreateCell(j, CellType.String);
                            if (dt.Tables[table].Rows[a][j] == DBNull.Value)
                            {
                                currentCell.SetCellValue("");
                            }
                            else
                            {
                                currentCell.SetCellValue(dt.Tables[table].Rows[a][j].ToString());
                            }
                            currentCell.CellStyle = style;
                            if (dt.Tables[table].Rows[a][j].ToString().Length > 28)
                            {
                                height = dt.Tables[table].Rows[a][j].ToString().Length / 29 > 0 ? dt.Tables[table].Rows[a][j].ToString().Length / 29 : 0;
                            }
                        }
                        //自适应行高
                        rowcontent.HeightInPoints = height == 0 ? 29 : height * 12 + 29;
                    }

                    //尾行
                    IRow rowEnd = sheet1.CreateRow(row);  //定义行 尾行
                    rowEnd.HeightInPoints = 30;
                    ICell rowEnd_ICell = rowEnd.CreateCell(0, CellType.String);
                    rowEnd_ICell.SetCellValue("制表人");
                    rowEnd_ICell.CellStyle = StyleLeft;

                    ICell rowEnd_ICell2 = rowEnd.CreateCell(3, CellType.String);
                    rowEnd_ICell2.SetCellValue("制表日期：");
                    rowEnd_ICell2.CellStyle = StyleLeft;

                    sheet1.AddMergedRegion(new CellRangeAddress(row, row, 0, 2));
                    sheet1.AddMergedRegion(new CellRangeAddress(row, row, 3, 5));

                    table++;
                    sheet1.SetRowBreak(row);
                    row++;
                }
                using (Stream stream = File.OpenWrite(filepath))
                {
                    hssfworkbook.Write(stream);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
